package com.xyh.dao.impl;

import com.xyh.bean.Area;
import com.xyh.bean.Insect;
import com.xyh.bean.ManagerClass;
import com.xyh.dao.IAreaDao;
import com.xyh.service.IClassService;
import com.xyh.service.impl.ClassServiceImpl;
import com.xyh.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: XiongYuhao
 * @Date: 2020/10/25 0025-下午 12:20
 * @Description: MyForestManagerSystem
 * @version：1.0
 */
public class AreaDaoImpl implements IAreaDao {
    IClassService imc = new ClassServiceImpl();
    @Override
    public int areaCounts(String areaName, String treeType, String className) {
        int counts = -1;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            String sql = "select count(0) from t_area inner join t_class on t_area.pk_id=t_class.pk_id where 1=1 ";
            if (areaName != null && !areaName.equalsIgnoreCase("")) {
                sql += " and f_areaName like '%" + areaName + "%'";
            }
            if (treeType != null && !treeType.equalsIgnoreCase("")) {
                sql += " and f_treeType like '%" + treeType + "%'";
            }
            if (className != null && !className.equalsIgnoreCase("")) {
                sql += " and f_className like '%" + className + "%'";
            }
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                counts = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return counts;
    }

    @Override
    public List<Area> listByPage(int pageNo, int pageSize, String areaName, String treeType, String className) {
        List<Area> areas = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            StringBuffer sql = new StringBuffer("select * from t_area left join t_class on t_area.pk_id=t_class.pk_id where 1=1 ");
            if (areaName != null && !areaName.equalsIgnoreCase("")) {
                sql.append(" and f_areaName like '%" + areaName + "%'");
            }
            if (treeType != null && !treeType.equalsIgnoreCase("")) {
                sql.append(" and f_treeType like '%" + treeType + "%'");
            }
            if (className != null && !className.equalsIgnoreCase("")) {
                sql.append(" and f_className like '%" + className + "%'");
            }
            sql.append(" limit " + (pageNo - 1) * pageSize + "," + pageSize);

            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Area area = new Area();
                area.setAreaId(rs.getInt("t_area.pk_id"));
                area.setAreaName(rs.getString("f_areaName"));
                area.setTreeType(rs.getString("f_treeType"));
                area.setLand(rs.getString("f_land"));
                area.setAdvTree(rs.getString("f_advTree"));

                ManagerClass mc = new ManagerClass();
                mc.setClassId(rs.getInt("t_class.pk_id"));
                mc.setClassName(rs.getString("f_className"));
                mc.setManagerPeople(rs.getString("f_managerPeople"));
                mc.setTel(rs.getString("f_tel"));
                mc.setNum(rs.getInt("f_number"));
                mc.setCreateTime(rs.getString("f_createTime"));
                mc.setArea(area);
                area.setMc(mc);
                areas.add(area);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return areas;
    }

    @Override
    public int add(Area area) {
        int line = -1;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConn();
            String sql = "insert into t_area (f_areaName,f_treeType,f_land,f_advTree) values (?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,area.getAreaName());
            ps.setString(2,area.getTreeType());
            ps.setString(3,area.getLand());
            ps.setString(4,area.getAdvTree());
            line = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(null, ps, conn);
        }
        return line;
    }

    @Override
    public List<Area> queryAreaList() {
        List<Area> areas = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            StringBuffer sql = new StringBuffer("select * from t_area left join t_class on t_area.pk_id=t_class.pk_id where t_class.pk_id is null ");
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                Area area = new Area();
                area.setAreaId(rs.getInt("t_area.pk_id"));
                area.setAreaName(rs.getString("f_areaName"));
                area.setTreeType(rs.getString("f_treeType"));
                area.setLand(rs.getString("f_land"));
                area.setAdvTree(rs.getString("f_advTree"));
                ManagerClass mc = new ManagerClass();
                mc.setClassId(rs.getInt("t_class.pk_id"));
                mc.setClassName(rs.getString("f_className"));
                mc.setManagerPeople(rs.getString("f_managerPeople"));
                mc.setTel(rs.getString("f_tel"));
                mc.setNum(rs.getInt("f_number"));
                mc.setCreateTime(rs.getString("f_createTime"));
                area.setMc(mc);
                areas.add(area);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, conn);
        }
        return areas;
    }
}
